package com.info.zhiduoduo.repository.mapper.maker;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.maker.MakerAlipayAvailableDto;
import com.info.zhiduoduo.repository.dto.maker.MakerAlipayDto;
import com.info.zhiduoduo.repository.entity.maker.MakerAlipay;

import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2021-07-10
 */
public interface MakerAlipayMapper extends BaseMapper<MakerAlipay> {

	@Select(" select map.*,c.compname,ma.realname,ma.is_registered_studio,ma.maker_label,ms.username,ms.invoicescope,c.friendly_state," +
			" CASE " +
			"   WHEN ms.taxer = 1  AND m.maker_type != 2 THEN 4 " +
			"   ELSE m.maker_type " +
			" END as makerType," +
			" CASE " +
			"   WHEN m.state = 4 THEN 3 " +
			"   WHEN ms.open_tax_acct = 1 THEN 5 " +
			"   WHEN ms.state = 2 THEN 4 " +
			"   WHEN ma.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState," +
			" ma.temporary_login as temporaryLogin," +
			" IF(m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '', 1, 0) as voluntary," +
			" ma.expert_label_state as expertLabelState," +
			" CASE WHEN mal.userid > 0 THEN 1 ELSE 0 END as isOpenAlipay," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" ma.opening_location as openingLocation," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" ma.idno "+
			" from zdd_maker_alipay map " +
			" join zdd_maker m on m.userid = map.userid " +
			" join zdd_maker_account ma on ma.userid = map.userid " +
			" join zdd_maker_studio ms on ms.userid = map.userid " +
			" left join zdd_company c on m.bindcompid = c.compid " +
			" left join zdd_maker_alipay mal on mal.userid = m.userid and mal.deleted=0 " +
			" left join zdd_partner_account pa on pa.studio_id = m.userid " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = ms.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" where map.deleted=#{deleted} and m.state != 4 " +
			" and c.compname like '%${compname}%' " +
			" and CONCAT(map.alipay_logon_id, ma.realname, ms.username) like '%${name}%' " +
			" and if(isnull(#{from}), 1=1, map.createtime >= #{from})" +
			" and if(isnull(#{to}), 1=1, map.createtime <= #{to})" +
			" and if(isnull(#{makerLabel}), 1=1, ma.maker_label like '%${makerLabel}%')" +
			" and if(isnull(#{friendlyState}),1=1,c.friendly_state=#{friendlyState})" +
			" and ms.invoicescope like '%${invoicescope}%'" +
			" order by map.createtime desc ")
	List<MakerAlipayDto> list(String compname, String name, Date from, Date to,String makerLabel,Integer friendlyState,String invoicescope,int deleted);

	@Select(" select map.*,c.compname,ma.realname,ma.is_registered_studio,ma.maker_label,ms.username,ms.invoicescope,epi.project_type," +
			" c.friendly_state,ma.opening_location," +
			" maa.isv_id,ap.name as isvName,maa.status as agreementStatus,ifnull(maab.available_amount, 0) as alipayAvailableAmount, " +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2 " +
			" from zdd_maker_alipay map " +
			" join zdd_maker m on m.userid = map.userid " +
			" join zdd_maker_account ma on ma.userid = map.userid " +
			" join zdd_maker_studio ms on ms.userid = map.userid " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = m.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" left join zdd_company c on m.bindcompid = c.compid " +
			" left join (select entrustee_id,project_type from zdd_entrustment_project_item GROUP BY entrustee_id)epi on epi.entrustee_id = map.userid" +
			" join zdd_maker_alipay_agreement maa on maa.userid = map.userid and maa.status = 2 and maa.test_passed = 1 " +
			" join zdd_maker_alipay_account_book maab on maab.userid = map.userid " +
			" join zdd_maker_alipay_principal ap on ap.id = maa.isv_id " +
			" left join zdd_maker_rescission_apply mrp on map.userid = mrp.userid and mrp.state = 1 " +
			" where map.deleted=0 and m.state != 4 and ms.open_tax_acct = 1 and mrp.id is null " +
			" and if(#{isvId}=0, 1=1, maa.isv_id = #{isvId}) " +
			" and c.compname like '%${compname}%' " +
			" and CONCAT(map.alipay_logon_id, ma.realname, ms.username) like '%${name}%' " +
			" and if(isnull(#{from}), 1=1, map.createtime >= #{from})" +
			" and if(isnull(#{to}), 1=1, map.createtime <= #{to})" +
			" and if(isnull(#{makerLabel}), 1=1, ma.maker_label like '%${makerLabel}%')" +
			" and if(isnull(#{projectType}), 1=1, epi.project_type in (#{projectType}))" +
			" and if(isnull(#{friendlyState}),1=1,c.friendly_state=#{friendlyState})" +
			" and ms.invoicescope like '%${invoicescope}%'" +
			" order by alipayAvailableAmount desc ")
	List<MakerAlipayAvailableDto> listAvailableAlipay(int isvId, String compname, String name,
													  String makerLabel,String projectType,Integer friendlyState,
													  String invoicescope,
													  Date from, Date to);

	@Select(" select map.*,c.compname,ma.realname,ma.is_registered_studio,ms.username " +
			" from zdd_maker_alipay map " +
			" join zdd_maker m on m.userid = map.userid " +
			" join zdd_maker_account ma on ma.userid = map.userid " +
			" join zdd_maker_studio ms on ms.userid = map.userid " +
			" join zdd_maker_alipay_agreement maa on maa.userid = map.userid and maa.status = 2 and maa.test_passed = 1 " +
			" join zdd_company c on m.bindcompid = c.compid " +
			" where map.deleted=0 and m.state != 4 " +
			" and c.compid = #{compid} " +
			" and CONCAT(ma.realname, ms.username) like '%${name}%' " +
			" order by map.createtime desc ")
	List<MakerAlipayDto> listByCompid(int compid, String name);

	@Select(" select map.*,c.compname,ma.realname,ma.is_registered_studio,ms.username " +
			" from zdd_maker_alipay map " +
			" join zdd_maker m on m.userid = map.userid " +
			" join zdd_maker_account ma on ma.userid = map.userid " +
			" join zdd_maker_studio ms on ms.userid = map.userid " +
			" join zdd_maker_alipay_agreement maa on maa.userid = map.userid and maa.status = 2 and maa.test_passed = 1 " +
			" join zdd_company c on m.bindcompid = c.compid " +
			" where map.deleted=0 and c.compid = #{compid} " +
			" and ms.username = #{username} " +
			" order by map.createtime desc " +
			" limit 1 ")
	MakerAlipayDto getValidByUsername(int compid, String username);
}
